/* ***** BEGIN LICENSE BLOCK *****
 *   Version: MPL 1.1/GPL 2.0/LGPL 2.1
 *
 * The contents of this file are subject to the Mozilla Public License Version
 * 1.1 (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 * http://www.mozilla.org/MPL/
 * 
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 *
 * The Original Code is Seoprofesional
 *
 * The Initial Developer of the Original Code is
 * Petr Blahoš.
 * Portions created by the Initial Developer are Copyright (C) 2009
 * the Initial Developer. All Rights Reserved.
 *
 * Contributor(s):
 *
 * Alternatively, the contents of this file may be used under the terms of
 * either the GNU General Public License Version 2 or later (the "GPL"), or
 * the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
 * in which case the provisions of the GPL or the LGPL are applicable instead
 * of those above. If you wish to allow use of your version of this file only
 * under the terms of either the GPL or the LGPL, and not to allow others to
 * use your version of this file under the terms of the MPL, indicate your
 * decision by deleting the provisions above and replace them with the notice
 * and other provisions required by the GPL or the LGPL. If you do not delete
 * the provisions above, a recipient may use your version of this file under
 * the terms of any one of the MPL, the GPL or the LGPL.
 * 
 * ***** END LICENSE BLOCK ***** */

/**
 * Now the logic of the main table is as follows:
 * If the value of the rank for the page is new or different from the previous one,
 * 2 values are inserted, the second one is 1s fresher then the first one.
 * If we test the same page again and get the same rank, we can just update the last
 * one, to make the time-span of the rank longer and not waste the database space.
 * 

select url, rank, datetime(tm/1000, 'unixepoch', 'localtime'), value from ranks join urls using (uid) order by url, rank, tm; 
select url, uid, rank, tm, datetime(tm/1000, 'unixepoch', 'localtime'), value from ranks join urls using (uid) order by url, rank, tm;
insert into ranks (uid, rank, tm, value) values (20, 4, 1245000000000, 22222);

DATABASE VERSIONS:
1.0
1.1: SRank is now 0-10 instead of 0-100. When upgrading, all values are divided by 100.
 */
EXPORTED_SYMBOLS = [
    "seopdb",
    "seop_global"
];

var consoleService = Components.classes["@mozilla.org/consoleservice;1"].getService(Components.interfaces.nsIConsoleService);
LOG = function(msg) {
    consoleService.logStringMessage("seop: " + msg);
}

var seopdb = {
conn: null,
init: function() {
    var file = Components.classes["@mozilla.org/file/directory_service;1"]
                     .getService(Components.interfaces.nsIProperties)
                     .get("ProfD", Components.interfaces.nsIFile);
    file.append("seop.sqlite");

    var storageService = Components.classes["@mozilla.org/storage/service;1"]
                        .getService(Components.interfaces.mozIStorageService);
    var createdb = 0;
    try {
        this.conn = storageService.openDatabase(file);
        // check whether we have got the tables
        var statement = this.conn.createStatement("SELECT count(*) FROM sqlite_master WHERE type='table' and name='whitelist' ");
        while (statement.executeStep()) {
            var value = statement.getInt32(0);
            if (!value) {
                // initialize the database
                createdb = 1;
            }
        }
        statement.finalize();
    } catch(e) {
        LOG("Exception: " + e);
        createdb = 1;
        file.remove(false);
        this.conn = storageService.openDatabase(file);
    }
    if (createdb) {
        this.createdb();
    } else {
        this.dbVersionUpdate();
    }
},
createdb: function() {
    LOG("Creating database");
    this.conn.executeSimpleSQL("CREATE TABLE whitelist (url TEXT PRIMARY KEY)");
    this.conn.executeSimpleSQL("CREATE TABLE urls (uid INTEGER PRIMARY KEY, url TEXT)");
    this.conn.executeSimpleSQL("CREATE TABLE ranks (uid INTEGER NOT NULL, rank INTEGER, tm INTEGER, value REAL)");
    this.conn.executeSimpleSQL("CREATE TABLE version (version STRING)");
    this.conn.executeSimpleSQL("INSERT INTO version (version) values ('v1.1')");
    LOG("Creating database - done");
},
dbVersionUpdate: function() {
    var has_version = 1;
    var statement = this.conn.createStatement("SELECT count(*) FROM sqlite_master WHERE type='table' and name='version' ");
    while (statement.executeStep()) {
        if (!statement.getInt32(0)) {
            // initialize the database
            has_version = 0;
        }
    }
    statement.finalize();
    if (!has_version) {
        LOG("Creating version table");
        this.conn.executeSimpleSQL("CREATE TABLE version (version STRING)");
        this.conn.executeSimpleSQL("INSERT INTO version (version) values ('v1.0')");
        LOG("Creating version table - done");
    }
    var version = "v1.0";
    has_version = 0;
    statement = this.conn.createStatement("SELECT version FROM version");
    if (statement.executeStep()) {
        version = statement.getString(0);
        has_version = 1;
    }
    statement.finalize();
    LOG("db version upgrade from " + version);
    if ("v1.0"==version) {
        // to 1.1: srank/= 10;
        LOG("Upgrading SRANK=SRANK/10");
        this.conn.executeSimpleSQL("UPDATE ranks SET value=value/10 WHERE rank=2");
        if (has_version) {
            this.conn.executeSimpleSQL("UPDATE version SET version='v1.1' ");
        } else {
            this.conn.executeSimpleSQL("INSERT INTO version (version) values ('v1.1')");
        }
    }
},
/*
 * Adds a record to the whitelist.
 */
whitelist: function(head) {
    var s1 = this.conn.createStatement("INSERT INTO whitelist (url) VALUES (?1)");
    s1.bindStringParameter(0, head);
    s1.execute();
    s1.finalize();
},
is_whitelisted: function(url) {
    var longest = "";
    var s1 = this.conn.createStatement("SELECT url FROM whitelist WHERE url=substr(?1, 1, length(url))");
    s1.bindStringParameter(0, url);
    while (s1.executeStep()) {
        var s = s1.getString(0);
        if (s.length>longest.length) {
            longest = s;
        }
    }
    s1.finalize();
    return longest;
},
un_whitelist: function(url) {
    var longest = "";
    var s1 = this.conn.createStatement("SELECT url FROM whitelist WHERE url=substr(?1, 1, length(url))");
    s1.bindStringParameter(0, url);
    while (s1.executeStep()) {
        var s = s1.getString(0);
        if (s.length>longest.length) {
            longest = s;
        }
    }
    s1.finalize();
    if (""!=longest) {
        var s1 = this.conn.createStatement("DELETE FROM whitelist WHERE url=?1");
        s1.bindStringParameter(0, longest);
        s1.execute();
        s1.finalize();
        LOG("Deleted " + longest);
    }
},
add_rank: function(url, rank, val) {
    if (""==this.is_whitelisted(url)) {
        LOG("add_rank: ignoring not whitelisted " + url);
        return ;
    }
    var uid = null;
    var s1 = this.conn.createStatement("SELECT uid FROM urls WHERE url=?1");
    s1.bindStringParameter(0, url);
    while (s1.executeStep()) {
        uid = s1.getInt32(0);
    }
    var insert = 1;
    s1.finalize();
    var row_id = null;
    var last_value = null;
    if (null==uid) {
        // insert
        s1 = this.conn.createStatement("INSERT INTO urls (url) VALUES (?1)");
        s1.bindStringParameter(0, url);
        s1.execute();
        s1.finalize();
        uid = this.conn.lastInsertRowID;
    } else {
        // was the last one the same as the one being inserted? Then just update the time.
        s1 = this.conn.createStatement("SELECT rowid, value FROM ranks WHERE uid=?1 AND rank=?2 ORDER BY tm DESC LIMIT 1");
        s1.bindInt32Parameter(0, uid);
        s1.bindInt32Parameter(1, rank);
        while (s1.executeStep()) {
            row_id = s1.getInt64(0);
            last_value = s1.getDouble(1);
        }
        s1.finalize();
        if (null!=last_value && last_value==val) {
            insert = 0;
            // and row_id is valid
        }
    }
    if (insert) {
        var dt = (new Date()).getTime();
        s1 = this.conn.createStatement("INSERT INTO ranks VALUES (?1, ?2, ?3, ?4)");
        s1.bindInt32Parameter(0, uid);
        s1.bindInt32Parameter(1, rank);
        s1.bindInt64Parameter(2, dt);
        s1.bindDoubleParameter(3, val);
        s1.execute();
        s1.bindInt32Parameter(0, uid);
        s1.bindInt32Parameter(1, rank);
        s1.bindInt64Parameter(2, dt+1);
        s1.bindDoubleParameter(3, val);
        s1.execute();
        s1.finalize();
    } else {
        var dt = (new Date()).getTime();
        s1 = this.conn.createStatement("UPDATE ranks SET tm=?1 WHERE rowid=?2");
        s1.bindInt64Parameter(0, dt);
        s1.bindInt64Parameter(1, row_id);
        s1.execute();
        s1.finalize();
    }
},
get_rank: function(url, rank) {
    var ret = null;
    var s1 = this.conn.createStatement(
        "SELECT ranks.value, ranks.tm FROM ranks, urls " + 
        "WHERE ranks.uid=urls.uid AND ranks.rank=?1 AND urls.url=?2 " + 
        "ORDER BY tm DESC LIMIT 1");
    s1.bindInt32Parameter(0, rank);
    s1.bindStringParameter(1, url);
    if (s1.executeStep()) {
        var tm = new Date();
        tm.setTime(s1.getInt64(1));
        ret = [ s1.getDouble(0), tm ];
    }
    s1.finalize();
    return ret;
},
get_tl: function(url, rank) {
    var ret = [];
    var s1 = this.conn.createStatement(
        "SELECT ranks.value, ranks.tm FROM ranks, urls " + 
        "WHERE ranks.uid=urls.uid AND ranks.rank=?1 AND urls.url=?2 " + 
        "ORDER BY tm");
    s1.bindInt32Parameter(0, rank);
    s1.bindStringParameter(1, url);
    while (s1.executeStep()) {
        var val = s1.getDouble(0);
        var tm = new Date();
        tm.setTime(s1.getInt64(1));
        tm.setSeconds(0);
        tm.setMinutes(0);
        tm.setHours(10);
        tm.setMilliseconds(0);
        ret.push([ tm, val]);
    }
    s1.finalize();
    return ret;
},
get_whitelist: function() {
    // returns the whitelist, sorted alphabetically
    var ret = [];
    var s1 = this.conn.createStatement("SELECT url FROM whitelist ORDER BY url");
    while (s1.executeStep()) {
        ret.push(s1.getString(0));
    }
    s1.finalize();
    return ret;
},
get_sub: function(root) {
    var ret = [];
    var s1 = this.conn.createStatement("SELECT url FROM urls WHERE ?=substr(url, 1, ?) ORDER BY url");
    s1.bindStringParameter(0, root);
    s1.bindInt32Parameter(1, root.length);
    while (s1.executeStep()) {
        ret.push(s1.getString(0));
    }
    s1.finalize();
    return ret;
},
forget_record: function(url) {
    var uid = null;
    var s1 = this.conn.createStatement("SELECT uid FROM urls WHERE ?=url");
    s1.bindStringParameter(0, url);
    if (s1.executeStep()) {
        uid = s1.getInt32(0);
    }
    s1.finalize();
    if (null!=uid) {
        s1 = this.conn.createStatement("DELETE FROM urls WHERE ?=uid");
        s1.bindInt32Parameter(0, uid);
        s1.execute();
        s1.finalize();
        s1 = this.conn.createStatement("DELETE FROM ranks WHERE ?=uid");
        s1.bindInt32Parameter(0, uid);
        s1.execute();
        s1.finalize();
    }
},
killorphans: function() {
    var s1 = seopdb.conn.createStatement("SELECT uid, url FROM urls");
    var s2 = seopdb.conn.createStatement("DELETE FROM ranks WHERE ?=uid");
    var s3 = seopdb.conn.createStatement("DELETE FROM urls WHERE ?=uid");
    while (s1.executeStep()) {
        var url = s1.getString(1);
        if (!seopdb.is_whitelisted(url)) {
            var uid = s1.getInt32(0);
            LOG("DEL: " + uid + ": " + url);
            s2.bindInt32Parameter(0, uid);
            s2.execute();
            s3.bindInt32Parameter(0, uid);
            s3.execute();
        }
    }
    s3.finalize();
    s2.finalize();
    s1.finalize();
},
vacuum: function () {
    LOG("Going to vacuum");
    seopdb.conn.executeSimpleSQL("VACUUM");
    LOG("vacuum done");
},
__sentinel: 0 //so that we can write a , after every bloody member.
};

var seop_global = {
fetchers: null,
date_format: "%d.%m.%y",
menulinks: [],
_x_sentinel_: 0
};

seopdb.init();

